﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace HRDexCapstoneProject.Functions.Forms
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        DataTable dt = new DataTable("dt");

        //Session String
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            txtStatus.Enabled = false;
            ddlStatus.Visible = false;
            Selected();
            SqlCommand cmd3 = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name," +
                "catPositionsID, DeptID, CollegeID FROM formEmployees WHERE (EmployeeID=" + SessionID() + ")", conn);
            SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
            DataTable dt3 = new DataTable("dt3");

            da3.Fill(dt3);

            txtFullName.Text = dt3.Rows[0].ItemArray[1].ToString();
            txtEmpNo.Text = dt3.Rows[0].ItemArray[0].ToString();
            ddlDept.SelectedValue = dt3.Rows[0].ItemArray[3].ToString();
            ddlCollege.SelectedValue = dt3.Rows[0].ItemArray[4].ToString();
            ddlPos.SelectedValue = dt3.Rows[0].ItemArray[2].ToString();

            txtFullName.Enabled = false;
            txtEmpNo.Enabled = false;
            ddlDept.Enabled = false;
            ddlCollege.Enabled = false;
            ddlPos.Enabled = false;

        }

        //Submit
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string formCBAID = Request.QueryString["formCBAID"];
            
            if (txtCBAid.Text != formCBAID)
            {
                SqlCommand cmdd = new SqlCommand("INSERT Into formCBA (StudentNo, DateSubmitted, CourseID, Year, StudentName, " +
                    "EmployeeID, DepartmentID, CollegeID,  pDiscount, catPositionsID,Cleared,Enrollment, Units )" +
                    "VALUES (@sNo,@date,@courseID, @year, @sName,@empID,@deptID, @collegeID, @pDiscount, @posID, @cleared,@enrollment, " +
                    "@units)", conn);
                SqlDataAdapter daa = new SqlDataAdapter(cmdd);

                conn.Open();

                cmdd.Parameters.Add("@sNo", SqlDbType.Int).Value = txtStudentNo.Text;
                cmdd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now;
                cmdd.Parameters.Add("@courseID", SqlDbType.Int).Value = dllCourse.Text;
                cmdd.Parameters.Add("@year", SqlDbType.VarChar).Value = ddlYear.Text;
                cmdd.Parameters.Add("@sName", SqlDbType.VarChar).Value = txtFullName.Text;
                cmdd.Parameters.Add("@empID", SqlDbType.Int).Value = txtEmpNo.Text;
                cmdd.Parameters.Add("@deptID", SqlDbType.Int).Value = ddlDept.SelectedValue;
                cmdd.Parameters.Add("@collegeID", SqlDbType.Int).Value = ddlCollege.SelectedValue;
                cmdd.Parameters.Add("@posID", SqlDbType.Int).Value = ddlPos.SelectedValue;
                cmdd.Parameters.Add("@pDiscount", SqlDbType.VarChar).Value = txtPercentageDiscount.Text;
                cmdd.Parameters.Add("@cleared", SqlDbType.VarChar).Value = txtCleared.Text;
                cmdd.Parameters.Add("@enrollment", SqlDbType.VarChar).Value = txtEnrollment.Text;
                cmdd.Parameters.Add("@units", SqlDbType.VarChar).Value = txtUnits.Text;

                cmdd.ExecuteNonQuery();
            }
            else
            {
                Edit();
            }
        }

        public void Selected()
        {
            string formCBAID = Request.QueryString["formCBAID"];

            if (formCBAID != null)
            {

                txtCBAid.Enabled = false;
                txtCleared.Enabled = false;
                txtDate.Enabled = false;
                txtEmpNo.Enabled = false;
                txtEnrollment.Enabled = false;
                txtFullName.Enabled = false;
                txtPercentageDiscount.Enabled = false;
                txtStatus.Enabled = false;
                txtStudentNo.Enabled = false;
                txtUnits.Enabled = false;
                dllCourse.Visible = false;
                ddlStatus.Enabled = false;
                ddlYear.Visible = false;


                SqlCommand cmd5 = new SqlCommand("SELECT formCBA.formCBAID, formCBA.StudentNo, formCBA.DateSubmitted, catCourse.CourseName, formCBA.Year, formCBA.StudentName, formCBA.pDiscount, formCBA.Cleared, formCBA.Enrollment, formCBA.Units, formEmployees.EmployeeID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formCBA.DepartmentID, formCBA.CollegeID, formCBA.catPositionsID, formCBA.AttachmentName, formCBA.ApprovalID FROM formCBA INNER JOIN catCourse ON formCBA.CourseID = catCourse.CourseID INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID INNER JOIN catDepartment ON formCBA.DepartmentID = catDepartment.DeptID INNER JOIN catCollege ON formCBA.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formCBA.catPositionsID = catPositions.catPositionsID WHERE (formCBA.formCBAID =" + formCBAID + ")", conn);
                //SELECT formCBA.formCBAID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, formCBA.StudentNo, formCBA.StudentName, formCBA.ApprovalID FROM formCBA INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID WHERE (formCBA.EmployeeID = @EmployeeID)
                SqlDataAdapter da5 = new SqlDataAdapter(cmd5);
                DataTable dt5 = new DataTable("dt5");
                da5.Fill(dt5);
 
                txtCBAid.Text = dt5.Rows[0].ItemArray[0].ToString();
                txtStudentNo.Text = dt5.Rows[0].ItemArray[1].ToString();
                txtDate.Text = dt5.Rows[0].ItemArray[2].ToString();
                dllCourse.SelectedValue = dt5.Rows[0].ItemArray[3].ToString();
                ddlYear.SelectedValue = dt5.Rows[0].ItemArray[4].ToString();
                txtFullName.Text = dt5.Rows[0].ItemArray[5].ToString();
                txtPercentageDiscount.Text = dt5.Rows[0].ItemArray[6].ToString();
                txtCleared.Text = dt5.Rows[0].ItemArray[7].ToString();
                txtEnrollment.Text = dt5.Rows[0].ItemArray[8].ToString();
                txtUnits.Text = dt5.Rows[0].ItemArray[9].ToString();
                txtEmpNo.Text = dt5.Rows[0].ItemArray[10].ToString();
                txtFullName.Text = dt5.Rows[0].ItemArray[11].ToString();
                ddlDept.SelectedValue  = dt5.Rows[0].ItemArray[12].ToString();
                ddlCollege.SelectedValue = dt5.Rows[0].ItemArray[13].ToString();
                ddlPos.SelectedValue = dt5.Rows[0].ItemArray[14].ToString();
                txtStatus.Text = dt5.Rows[0].ItemArray[16].ToString();

                if (dt5.Rows[0].ItemArray[16].ToString() == "0")
                {
                    ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                    ddlStatus.Enabled = false;
                }
                else if (dt5.Rows[0].ItemArray[16].ToString() == "1")
                {
                    ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                    ddlStatus.Enabled = false;
                }
                else
                {
                    ddlStatus.Enabled = true;
                }

                SqlCommand cmd2 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName, "+
                    "catPositions.catPositionsName, catDepartment.DepartmentName FROM formEmployees INNER JOIN "+
                    "catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN "+
                    "catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID INNER JOIN catDepartment "+
                    "ON formEmployees.DeptID = catDepartment.DeptID WHERE (formEmployees.EmployeeID = "+SessionID()+")", conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable("dt2");
                da2.Fill(dt2);
                
                if (dt2.Rows[0].ItemArray[1].ToString() == "Admin")
                {
                    ddlStatus.Visible = true;
                    txtStatus.Visible = false;                   
                }
                else if (dt2.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt2.Rows[0].ItemArray[2].ToString() == "Head" && dt2.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits")
                {  
                    ddlStatus.Visible = true;
                    txtStatus.Visible = false;
                }
                else
                {
                    ddlStatus.Visible = false;
                    txtStatus.Enabled = false;
                    txtStatus.Visible = true;      
                }
            
            }


        }

        public void Edit()
        {
            string formCBAID = Request.QueryString["formCBAID"];
            SqlCommand cmd7 = new SqlCommand("UPDATE formCBA SET  ApprovalID = @status WHERE(formCBAID = "+ formCBAID +")",conn );
            SqlDataAdapter da7 = new SqlDataAdapter(cmd7);
            conn.Open();
            
            cmd7.Parameters.AddWithValue("@status", SqlDbType.Int).Value = ddlStatus.SelectedValue;
            
            cmd7.ExecuteNonQuery(); 
            conn.Close();
        }



    }
}
